﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;
using System.IO;
using ArticleManager.DataClasses;
using ArticleManager.Helpers;
using ADOX;

namespace ArticleManager
{
    public class DataHelper
    {
        # region Connection
        private OleDbConnection __Connection = null;
        private OleDbConnection _Connection
        {

            get
            {
                if (null == __Connection)
                {
                    //string databasepath = @"c:\Data\Tariq\" + @"Articles.mdb";

                    //string databasepath = Directory.GetCurrentDirectory() + @"\Articles.mdb";

                    string databasepath = Utils.GetUserDataPath() + @"\Articles.mdb";

                    if (!File.Exists(databasepath))
                    {
                        //Create the database.
                        ADOX.CatalogClass cat = new ADOX.CatalogClass();

                        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;"+
                             "Data Source=" +databasepath+";" +
                             "Jet OLEDB:Engine Type=5");

                        cat = null;
                    }

                    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+databasepath;
                    __Connection = new OleDbConnection(connectionString);

                    __Connection.Open();

                    //DataTable dt = __Connection.GetSchema();
                    DatabaseValidator validator = new DatabaseValidator(__Connection);
                    validator.CheckDatabaseCompatibility();
                }
                else
                {
                    while (ConnectionState.Executing == __Connection.State || ConnectionState.Fetching == __Connection.State)
                    {
                        System.Threading.Thread.Sleep(10);
                    }

                    if (ConnectionState.Open != __Connection.State)
                        __Connection.Open();
                }
                return __Connection;
            }
        }

        
        public void Close()
        {
            if (null != __Connection)
                if (ConnectionState.Closed != __Connection.State)
                {
                    __Connection.Close();
                    __Connection.Dispose();
                }
        }
        #endregion

        #region ArticleTitles
        private ArticleTitle MakeArticleTitle(OleDbDataReader reader)
        {
            int id = (int)reader[ArticleTitle.Properties.ArticleId];
            string title = reader[ArticleTitle.Properties.Title] as string;
            ArticleTitle articleTitle = new ArticleTitle(id, title);
            return articleTitle;
        }
        public IList GetArticleTitles()
        {
            ArrayList articles = new ArrayList();
            using (OleDbCommand cmd = new OleDbCommand("SELECT article_id,title FROM Articles ORDER BY article_id", _Connection))
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ArticleTitle article = MakeArticleTitle(reader);
                        articles.Add(article);
                    }
                    reader.Close();
                }
            }
            return articles;
        }

        #endregion

        #region ArticleAuthors
        private void PrepareCommandFromArticleAuthor(ArticleAuthor author, OleDbCommand cmd)
        {
            cmd.Parameters.Add(ArticleAuthor.Properties.ArticleId, OleDbType.Integer).Value = author.ArticleId;
            cmd.Parameters.Add(ArticleAuthor.Properties.AuthorId, OleDbType.Integer).Value = author.AuthorId;
        }

        private void AddNewArticleAuthor(ArticleAuthor key)
        {
            using (OleDbCommand cmd = new OleDbCommand(
                "INSERT INTO article_authors (article_id,author_id)" +
                " VALUES (@article_id, @author_id)", _Connection))
            {
                PrepareCommandFromArticleAuthor(key, cmd);

                try
                {
                    // Perform insert
                    int nRowsUpdate = cmd.ExecuteNonQuery();
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }

        }

        private void DeleteArticleAuthors(int article_id)
        {
            using (OleDbCommand cmd = new OleDbCommand("DELETE FROM article_authors where article_id = @article_id", _Connection))
            {

                cmd.Parameters.Add(ArticleAuthor.Properties.ArticleId, OleDbType.Integer).Value = article_id;

                try
                {
                    // Perform insert
                    int nRowsUpdate = cmd.ExecuteNonQuery();
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }

        }

        private ArticleAuthor MakeArticleAuthor(OleDbDataReader reader)
        {
            int art_id = (int)reader[ArticleAuthor.Properties.ArticleId];
            int key_id = (int)reader[ArticleAuthor.Properties.AuthorId];
            ArticleAuthor articlekey = new ArticleAuthor(art_id, key_id);
            return articlekey;
        }

        private ArticleAuthorsCollection GetArticleAuthors(int art_id)
        {
            ArticleAuthorsCollection articleauthors = new ArticleAuthorsCollection();

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM article_authors where article_id=@article_id", _Connection))
            {
                cmd.Parameters.Add(ArticleAuthor.Properties.ArticleId, OleDbType.Integer).Value = art_id;

                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ArticleAuthor articleauthor = MakeArticleAuthor(reader);
                        articleauthors.Add(articleauthor);
                    }
                    reader.Close();
                }
            }

            return articleauthors;
        }
        #endregion

        #region ArticleKeywords
        private void PrepareCommandFromArticleKey(ArticleKey key, OleDbCommand cmd)
        {
            cmd.Parameters.Add(ArticleKey.Properties.ArticleId, OleDbType.Integer).Value = key.ArticleId;
            cmd.Parameters.Add(ArticleKey.Properties.KeyId, OleDbType.Integer).Value = key.KeyId;
        }


        private void DeleteArticleKeys(int artcile_id)
        {
            using (OleDbCommand cmd = new OleDbCommand(
                "DELETE FROM article_keys where article_id = @article_id", _Connection))
            {
                cmd.Parameters.Add(ArticleKey.Properties.ArticleId, OleDbType.Integer).Value = artcile_id;

                try
                {
                    // Perform insert
                    int nRowsUpdate = cmd.ExecuteNonQuery();
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }

        }

        private void AddNewArticleKey(ArticleKey key)
        {
            using (OleDbCommand cmd = new OleDbCommand(
                "INSERT INTO article_keys (article_id,key_id)" +
                " VALUES (@article_id, @key_id)", _Connection))
            {
                PrepareCommandFromArticleKey(key, cmd);

                try
                {
                    // Perform insert
                    int nRowsUpdate = cmd.ExecuteNonQuery();
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }

        }

        private ArticleKey MakeArticleKey(OleDbDataReader reader)
        {
            int art_id = (int)reader[ArticleKey.Properties.ArticleId];
            int key_id = (int)reader[ArticleKey.Properties.KeyId];
            ArticleKey articlekey = new ArticleKey(art_id,key_id);
            return articlekey;
        }

        private ArticleKeysCollection GetArticleKeywords(int art_id)
        {
            ArticleKeysCollection articlekeys = new ArticleKeysCollection();

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM article_keys where article_id = @article_id ORDER BY key_id", _Connection))
            {
                cmd.Parameters.Add(ArticleKey.Properties.ArticleId, OleDbType.Integer).Value = art_id;

                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        ArticleKey articlekey = MakeArticleKey(reader);
                        articlekeys.Add(articlekey);
                    }
                    reader.Close();
                }
            }

            return articlekeys;
        }
        #endregion

        #region Articles
        private Article MakeArticle(OleDbDataReader reader)
        {
            int id = (int)reader[Article.Properties.ArtcileId];
            string title = reader[Article.Properties.Title] as string;
            string reviewers = reader[Article.Properties.Reviewers] as string;
            string publishers = reader[Article.Properties.Publishers] as string;
            string translators = reader[Article.Properties.Translator] as string;
            string text = reader[Article.Properties.Text] as string;
            ArticleKeysCollection keyList = new ArticleKeysCollection();
            ArticleAuthorsCollection authorList = new ArticleAuthorsCollection();
            Article article = new Article(id, title, reviewers,
                publishers, translators, text, keyList, authorList);

            return article;
        }


        public IList GetArticles()
        {
            ArrayList articles = new ArrayList();

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM Articles ORDER BY article_id", _Connection))
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Article article = MakeArticle(reader);
                        article.KeywordList = GetArticleKeywords(article.ArticleId);
                        article.AuthorsList = GetArticleAuthors(article.ArticleId);
                        articles.Add(article);
                    }
                    reader.Close();
                }
            }



            return articles;

        }

        public Article GetArticle(int id)
        {
            Article article = new Article();
            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM articles where article_id = @article_id", _Connection))
            {
                cmd.Parameters.Add(Article.Properties.ArtcileId, OleDbType.Integer).Value = id;
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        article = MakeArticle(reader);
                        article.KeywordList = GetArticleKeywords(article.ArticleId);
                        article.AuthorsList = GetArticleAuthors(article.ArticleId);
                    }
                    reader.Close();
                }
            }
            return article;
        }

        public bool AddNewArticle(ref Article article)
        {
            using (OleDbCommand cmd = new OleDbCommand(
                "INSERT INTO articles (title, reviewers, publishers, translator, atext)" +
                " VALUES (@title, @reviewers, @publishers, @translator, @text)", _Connection))
            {

                PrepareCommandFromArticle(article, cmd, false);

                try
                {
                    // Perform insert
                    cmd.ExecuteNonQuery();

                    // Get the recent ID
                    cmd.CommandText = "SELECT MAX(article_id) FROM articles";

                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            article.ArticleId = Convert.ToInt32(reader[0].ToString());
                            if (article.KeywordList.Count > 0)
                            {
                                foreach (ArticleKey akey in article.KeywordList)
                                {
                                    akey.ArticleId = article.ArticleId;
                                    AddNewArticleKey(akey);
                                }
                            }

                            if (article.AuthorsList.Count > 0)
                            {
                                foreach (ArticleAuthor akey in article.AuthorsList)
                                {
                                    akey.ArticleId = article.ArticleId;
                                    AddNewArticleAuthor(akey);
                                }
                            }
                        }
                        else
                        {
                            return false;
                        }

                    }

                    return true;
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }
        }

        public bool UpdateArtcile(ref Article article)
        {
            using (OleDbCommand cmd = new OleDbCommand(
                "UPDATE articles  set title=@title, reviewers=@reviewers, publishers=@publishers,"+
                " translator=@translator, atext=@atext Where article_id=@article_id", _Connection))
            {

                PrepareCommandFromArticle(article, cmd,true);

                try
                {
                    // Perform insert
                    cmd.ExecuteNonQuery();

                    DeleteArticleAuthors(article.ArticleId);
                    DeleteArticleKeys(article.ArticleId);

                    if (article.KeywordList.Count > 0)
                    {
                        foreach (ArticleKey akey in article.KeywordList)
                        {
                            akey.ArticleId = article.ArticleId;
                            AddNewArticleKey(akey);
                        }
                    }

                    if (article.AuthorsList.Count > 0)
                    {
                        foreach (ArticleAuthor akey in article.AuthorsList)
                        {
                            akey.ArticleId = article.ArticleId;
                            AddNewArticleAuthor(akey);

                        }
                    }

                    return true;
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }
        }


        protected void PrepareCommandFromArticle(Article article, OleDbCommand cmd, bool isForUpdate)
        {
            if (isForUpdate)
            {
                cmd.Parameters.Add(Article.Properties.ArtcileId, OleDbType.Integer).Value = article.ArticleId;
            }
            cmd.Parameters.Add(Article.Properties.Title, OleDbType.VarChar).Value = article.Title;
            cmd.Parameters.Add(Article.Properties.Reviewers, OleDbType.VarChar).Value = article.Reviewers;
            cmd.Parameters.Add(Article.Properties.Publishers, OleDbType.VarChar).Value = article.Publishers;
            cmd.Parameters.Add(Article.Properties.Translator, OleDbType.VarChar).Value = article.Translators;
            cmd.Parameters.Add(Article.Properties.Text, OleDbType.LongVarChar).Value = article.Text;

        }



        #endregion

        # region KeyWords
        public bool UpdateKeyword(Keyword keyword)
        {
            if (keyword == null)
            {
                return false;
            }

            using (OleDbCommand cmd = new OleDbCommand(
                "UPDATE keywords SET key_text = @key_text where id=@id", _Connection))
            {
                PrepareCommandFromKeyword(keyword, cmd);
                try
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (OleDbException ex)
                {
                    //ex.StackTrace;
                    //return false;
                    throw ex;
                }
            }
        }

        public bool DeleteKeyword(Keyword keyword)
        {
            if (keyword == null)
            {
                return false;
            }

            using (OleDbCommand cmd = new OleDbCommand(
                "DELETE FROM keywords WHERE id=" +
                " @id", _Connection))
            {
                PrepareCommandFromKeyword(keyword, cmd);

                try
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (OleDbException x)
                {
                    //x;
                    //return false;
                    throw x;
                }
            }
            

        }

        private void PrepareCommandFromKeyword(Keyword key, OleDbCommand cmd)
        {
            cmd.Parameters.Add(Keyword.Properties.KeyId, OleDbType.Integer).Value = key.KeyId;
            cmd.Parameters.Add(Keyword.Properties.KeyText, OleDbType.VarChar).Value = key.KeyText;
        }

        public bool AddNewKeyword(ref Keyword keyword)
        {
            using (OleDbCommand cmd = new OleDbCommand(
                "INSERT INTO keywords (key_text)" +
                " VALUES (@key_text)", _Connection))
            {
                PrepareCommandFromKeyword(keyword, cmd);

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT MAX(id) FROM keywords";
                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            keyword.KeyId = Convert.ToInt32(reader[0].ToString());
                        }
                        else
                        {
                            return false;
                        }
                    }
                    return true;
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }
        }     

        public string GetKeyText(int k_id)
        {
            string key_text = "";

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM keywords where id = @id", _Connection))
            {
                cmd.Parameters.Add(Keyword.Properties.KeyId, OleDbType.Integer).Value = k_id;
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        key_text+=reader[Keyword.Properties.KeyText] as string;
                    }
                    reader.Close();
                }
            }

            return key_text;

        }

        private Keyword MakeKeyword(OleDbDataReader reader)
        {
            int id = (int)reader[Keyword.Properties.KeyId];
            string text = reader[Keyword.Properties.KeyText] as string;

            Keyword keyword = new Keyword(id,text);

            return keyword;
        }


        public IList GetKeywords()
        {
            ArrayList keywords = new ArrayList();

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM keywords", _Connection))
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Keyword keyword = MakeKeyword(reader);
                        keywords.Add(keyword);
                    }
                    reader.Close();
                }
            }

            return keywords;

        }

        #endregion

        # region Authors
        public bool UpdateAuthor(Author author)
        {
            if (author == null)
            {
                return false;
            }

            using (OleDbCommand cmd = new OleDbCommand(
                "UPDATE authors SET author_name = @author_name where author_id=@author_id", _Connection))
            {
                PrepareCommandFromAuthor(author, cmd);
                try
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (OleDbException ex)
                {
                    //ex.StackTrace;
                    //return false;
                    throw ex;
                }
            }
        }

        public bool DeleteAuthor(Author author)
        {
            if (author == null)
            {
                return false;
            }

            using (OleDbCommand cmd = new OleDbCommand(
                "DELETE FROM authors WHERE author_id=" +
                " @author_id", _Connection))
            {
                PrepareCommandFromAuthor(author, cmd);

                try
                {
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (OleDbException x)
                {
                    //x;
                    //return false;
                    throw x;
                }
            }


        }

        private void PrepareCommandFromAuthor(Author author, OleDbCommand cmd)
        {
            cmd.Parameters.Add(Author.Properties.AuthorId, OleDbType.Integer).Value = author.AuthorId;
            cmd.Parameters.Add(Author.Properties.AuthorName, OleDbType.VarChar).Value = author.AuthorName;
        }

        public bool AddNewAuthor(ref Author author)
        {
            using (OleDbCommand cmd = new OleDbCommand(
                "INSERT INTO authors (author_name)" +
                " VALUES (@author_name)", _Connection))
            {
                PrepareCommandFromAuthor(author, cmd);

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT MAX(author_id) FROM authors";
                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            author.AuthorId = Convert.ToInt32(reader[0].ToString());
                        }
                        else
                        {
                            return false;
                        }
                    }
                    return true;
                }
                catch (OleDbException x)
                {
                    throw x;
                }
            }
        }

        public string GetAuthorName(int k_id)
        {
            string author_name = "";

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM authors where author_id = @author_id", _Connection))
            {
                cmd.Parameters.Add(Author.Properties.AuthorId, OleDbType.Integer).Value = k_id;
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        author_name += reader[Author.Properties.AuthorName] as string;
                    }
                    reader.Close();
                }
            }

            return author_name;

        }

        private Author MakeAuthor(OleDbDataReader reader)
        {
            int id = (int)reader[Author.Properties.AuthorId];
            string text = reader[Author.Properties.AuthorName] as string;

            Author author = new Author(id, text);

            return author;
        }


        public IList GetAuthors()
        {
            ArrayList authors = new ArrayList();

            using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM authors", _Connection))
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Author author = MakeAuthor(reader);
                        authors.Add(author);
                    }
                    reader.Close();
                }
            }

            return authors;

        }

        #endregion

        #region General
        public OleDbDataAdapter GetKeywordsAdapter(string command)
        {
            return new OleDbDataAdapter(command, this._Connection);
        }
        #endregion
    }
}


/*
 namespace ConsoleApplication1
{
    class Class1
    {
        [STAThread]
        static void Main(string[] args)
        {
            ADOX.CatalogClass cat = new ADOX.CatalogClass();

            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
                 "Data Source=D:\\AccessDB\\NewMDB.mdb;" +
                 "Jet OLEDB:Engine Type=5");

            Console.WriteLine("Database Created Successfully");

            cat = null;

        }
    }
}

6. Change the path to the new .mdb file as appropriate. Press F5 to build and
run the project.

The new .mdb file will be created in Access 2000 (Jet 4.0) format. For a
different Jet format, see "References."

 */